﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace Reports.RoznCashIncome
{
    public class DataLoader
    {
        public static void GetData(Reports.RoznCashIncome.DataSet.DataDataTable data, string ConnectionString, string depName, DateTime dateF, DateTime dateT, Int32 departId)
        {
            string QueryString = 
@"SELECT @DepartName as DepartName,
vwGoodsFlow.DocDate as Date,  
CD.CashRegisters.Name as CustomerName,
vwInputLines.RetailPrice * vwGoodsFlow.Qntty * (-1) as  SumTotal,
CASE WHEN (vwInputLines.NDSTax IS NULL)or(vwInputLines.NDSTax = 0) THEN vwGoodsFlow.RetailSum * - 1 ELSE 0 END AS SumVat0,
CASE WHEN vwInputLines.NDSTax = 10 THEN vwGoodsFlow.RetailSum * - 1 ELSE 0 END AS SumVat10, 
CASE WHEN vwInputLines.NDSTax = 18 THEN vwGoodsFlow.RetailSum * - 1 ELSE 0 END AS SumVat18,
vwInputLines.RetailPrice - vwInputLines.SupplierPrice AS  AptSum
FROM CD.CashRegisterShifts INNER JOIN
CA.Documents ON CD.CashRegisterShifts.Id = CA.Documents.CashRegisterShiftId INNER JOIN
vwGoodsFlow INNER JOIN
vwInputLines ON vwGoodsFlow.PartyId = vwInputLines.PartyId ON CA.Documents.Id = vwGoodsFlow.DocId INNER JOIN
CD.CashRegisters ON CD.CashRegisterShifts.CashRegisterId = CD.CashRegisters.Id
WHERE     (vwGoodsFlow.DocTypeId = 3) AND vwGoodsFlow.DocDate BETWEEN @DateF AND @DateT";
            string QueryString1 =
@"SELECT CONVERT(datetime, CONVERT(varchar, CA.Documents.DocDate, 104), 104)as Date,  
CD.CashRegisters.Name as CustomerName,
CA.GoodsDocLines.RetailSum* (-1) as  SumTotal,
CASE WHEN (CD.InputLines.NDSTax IS NULL)or(CD.InputLines.NDSTax = 0) THEN CA.GoodsDocLines.RetailSum * - 1 ELSE 0 END AS SumVat0,
CASE WHEN CD.InputLines.NDSTax = 10 THEN CA.GoodsDocLines.RetailSum * - 1 ELSE 0 END AS SumVat10, 
CASE WHEN CD.InputLines.NDSTax = 18 THEN CA.GoodsDocLines.RetailSum * - 1 ELSE 0 END AS SumVat18,
CD.Parties.RetailPrice - CD.InputLines.SupplierPrice AS  AptSum
FROM CA.Documents INNER JOIN
CA.GoodsDocLines ON CA.Documents.Id = CA.GoodsDocLines.DocId INNER JOIN
CD.Parties ON CA.GoodsDocLines.PartyId = CD.Parties.Id INNER JOIN
CD.InputLines ON CD.Parties.InputLineId = CD.InputLines.Id AND CD.Parties.InputLineId = CD.InputLines.Id INNER JOIN
CD.CashRegisterShifts ON CA.Documents.CashRegisterShiftId = CD.CashRegisterShifts.Id AND 
CA.Documents.CashRegisterShiftId = CD.CashRegisterShifts.Id AND CA.Documents.CashRegisterShiftId = CD.CashRegisterShifts.Id AND 
CA.Documents.CashRegisterShiftId = CD.CashRegisterShifts.Id INNER JOIN
CD.CashRegisters ON CD.CashRegisterShifts.CashRegisterId = CD.CashRegisters.Id AND 
CD.CashRegisterShifts.CashRegisterId = CD.CashRegisters.Id AND CD.CashRegisterShifts.CashRegisterId = CD.CashRegisters.Id
WHERE     (CA.Documents.DocTypeId = 3) AND CA.Documents.DepartmentId = @DepartmentId
AND CA.Documents.DocDate BETWEEN @DateF AND @DateT";
            string QueryString3 =
@"SELECT CA.GoodsDocLines.PartyId, 
CONVERT(datetime, CONVERT(varchar, CA.Documents.DocDate, 104), 104)as Date,  
CD.CashRegisters.Name as CustomerName,
CA.GoodsDocLines.RetailSum* (-1) as  SumTotal,
CASE WHEN (dbo.vwInputLines.NDSTax IS NULL)or(dbo.vwInputLines.NDSTax = 0) THEN CA.GoodsDocLines.RetailSum * - 1 ELSE 0 END AS SumVat0,
CASE WHEN dbo.vwInputLines.NDSTax = 10 THEN CA.GoodsDocLines.RetailSum * - 1 ELSE 0 END AS SumVat10, 
CASE WHEN dbo.vwInputLines.NDSTax = 18 THEN CA.GoodsDocLines.RetailSum * - 1 ELSE 0 END AS SumVat18,
dbo.vwInputLines.RetailPrice - dbo.vwInputLines.SupplierPrice AS  AptSum
FROM CA.Documents INNER JOIN
CA.GoodsDocLines ON CA.Documents.Id = CA.GoodsDocLines.DocId INNER JOIN
CD.Parties ON CA.GoodsDocLines.PartyId = CD.Parties.Id INNER JOIN
dbo.vwInputLines/*CD.InputLines*/ ON CD.Parties.InputLineId = dbo.vwInputLines.PartyId --and CD.InputLines.DocId = CD.Parties.DocId  
INNER JOIN CD.CashRegisterShifts ON CA.Documents.CashRegisterShiftId = CD.CashRegisterShifts.Id 
INNER JOIN CD.CashRegisters ON CD.CashRegisterShifts.CashRegisterId = CD.CashRegisters.Id 
WHERE     (CA.Documents.DocTypeId = 3) AND CA.Documents.DepartmentId = @DepartmentId
AND CA.Documents.DocDate BETWEEN @DateF AND @DateT 
--order by 8";
            string QueryString4 = @"SELECT gdl.PartyId, 
CONVERT(datetime, CONVERT(varchar, d.DocDate, 104), 104)as Date,  
cr.[Name] as CustomerName,
gdl.RetailSum* (-1) as  SumTotal,
CASE WHEN (il.NDSTax IS NULL)or(il.NDSTax = 0) THEN gdl.RetailSum * - 1 ELSE 0 END AS SumVat0,
CASE WHEN il.NDSTax = 10 THEN gdl.RetailSum * - 1 ELSE 0 END AS SumVat10, 
CASE WHEN il.NDSTax = 18 THEN gdl.RetailSum * - 1 ELSE 0 END AS SumVat18,
il.RetailPrice - il.SupplierPrice AS  AptSum,
(gdl.DiscountValue * il.RetailPrice)/100 as Disc--,gdl.DiscountValue,il.RetailPrice
FROM CA.Documents as d INNER JOIN
CA.GoodsDocLines as gdl ON d.Id = gdl.DocId INNER JOIN
CD.Parties ON gdl.PartyId = CD.Parties.Id INNER JOIN
dbo.vwInputLines as il ON CD.Parties.InputLineId = il.PartyId --and CD.InputLines.DocId = CD.Parties.DocId  
INNER JOIN CD.CashRegisterShifts ON d.CashRegisterShiftId = CD.CashRegisterShifts.Id 
INNER JOIN CD.CashRegisters as cr ON CD.CashRegisterShifts.CashRegisterId = cr.Id 
WHERE     (d.DocTypeId = 3) AND d.DepartmentId = @DepartmentId
AND d.DocDate BETWEEN @DateF AND @DateT ";
            try
            {
                using (SqlConnection connection = new SqlConnection(ConnectionString))
                {
                    SqlCommand command = new SqlCommand(QueryString4, connection);
                    SqlParameter DepartName = new SqlParameter("DepartName", SqlDbType.VarChar, 30);
                    DepartName.Value = depName;
                    command.Parameters.Add(DepartName);
                    SqlParameter DateF = new SqlParameter("@DateF", SqlDbType.DateTime);
                    DateF.Value = dateF;
                    command.Parameters.Add(DateF);
                    SqlParameter DateT = new SqlParameter("@DateT", SqlDbType.DateTime);
                    DateT.Value = dateT;
                    command.Parameters.Add(DateT);
                    SqlParameter DepartmentId = new SqlParameter("@DepartmentId", SqlDbType.Int);
                    DepartmentId.Value = departId;
                    command.Parameters.Add(DepartmentId);
                    SqlDataAdapter adapter = new SqlDataAdapter(command);
                    adapter.Fill(data);
                }
            }
            catch (SqlException se)
            {
                System.Windows.Forms.MessageBox.Show(se.Message);
            }
            catch (Exception ee)
            {
                System.Windows.Forms.MessageBox.Show(ee.Message);
            }

        }
    }
}
